package gbench.sandbox.data;

import org.junit.jupiter.api.Test;

import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.*;
import gbench.common.tree.LittleTree.IRecord;
import gbench.common.tree.LittleTree.KVPair;
import gbench.common.tree.LittleTree.SQL;
import gbench.commonApp.data.DataApp;

import static gbench.common.tree.LittleTree.IRecord.*;
import static gbench.common.tree.LittleTree.Tuple2.TUP2;
import static gbench.common.tree.LittleTree.*;

/**
 * 
 * @author gbench
 *
 */
public class DataAppAndPivotTable {
    /**
     * 
     * 
     * @author gbench
     *
     */
    public class MyDataApp extends DataApp {

        /**
         * 
         * @param name 数据库名称
         */
        public MyDataApp(String dbname) {
            this.dbname = dbname;
            this.reload(this.configureDbName(dbname));
        }

        /**
         * 数据查询
         * 
         * @param sql sql 语句
         * @return IRecord 集合
         */
        public List<IRecord> sqlquery(final String sql, String dbname) {
            reload(this.configureDbName(dbname));
            return this.jdbc.sql2records(sql);
        }

        /**
         * 数据查询
         * 
         * @param sql sql 语句
         * @return IRecord 集合
         */
        public List<IRecord> sqlquery(final String sql) {
            return this.sqlquery(sql, dbname);
        }

        /**
         * 配置数据库
         * 
         * @param dbname
         * @return 设定数据库
         */
        public IRecord configureDbName(final String dbname) {
            return cfg.derive("url", MFT("jdbc:mysql://localhost:3306/{0}?serverTimezone=GMT%2B8", dbname));
        }

        private String dbname; // 数据库名称

    }

    @Test
    public void foo() {
        final var dataApp = new MyDataApp("db1");// 打开一个数据库 db1,db1 需要在创建
        final var builder = IRecord.builder("id,transcode,name,drcr,amount,createTime,description");
        final var rec = builder.get(1l,1l, "name",0, 2000d, LocalDateTime.now(),"-");// 生成一个数据示意图
        final var database = dataApp.getDataBase(); // 获取数据库对象
        final var tableName = "entry";// 默认表名
        final var namedsql = new SQL(tableName, rec);
        final var sql = namedsql.createTable().get(2);
        final var ai = new AtomicInteger(0);
        System.out.println("创建数据表  sql:");
        System.out.println(sql);
        
        System.out.println("\n分录明细:");
        dataApp.withTransaction(sess -> {
            if(database.exists(tableName)) sess.sqlexecute("drop table entry");
            sess.sqlexecute(sql);
            final List<String> sqls = Stream.iterate(0, i->i+1).map(i->TUP2(i,Math.random()*10000)).limit(100)
                .flatMap(tup-> Stream.of ( // 生成借贷分录
                    new SQL(tableName,rec.derive("id",ai.getAndIncrement(),"transcode",tup._1(),"drcr",0,"name","营业收入","amount",tup._2())).insert()
                    ,new SQL(tableName,rec.derive("id",ai.getAndIncrement(),"transcode",tup._1(),"drcr",1,"name","库存现金","amount",tup._2())).insert()
                )).collect(Collectors.toList());
            for(var s:sqls) sess.sqlexecute(s);
            //
            final var recs = sess.sql2records("select * from entry");
            recs.forEach(e -> {
                System.out.println(e);
            });
            
            System.out.println("\ntrial balance:");
            final var result = recs.stream().collect(pvtclc("name,drcr")) // 根据科目名称, 借贷方向  分组的分组的 数据累加
            .dfs_clc( // 数据归并
                Collectors.summarizingDouble(e->((IRecord)e).dbl("amount")), // 归集amount 结果
                e->e.apply((KVPair<String,DoubleSummaryStatistics> kvp)->kvp.value().getSum())
            );// dfs_clc
            System.out.println(result);
        });
    }
}